import pandas as pd
import numpy as np
# import datetime as datetime
import warnings
import openpyxl
from openpyxl import load_workbook
from decimal import Decimal as decimal

from datetime import datetime, timedelta
import calendar
import xlrd
import xlwt

today = datetime.today()
first_day_of_this_month = today.replace(day=1)
print(first_day_of_this_month)
last_day_of_month = datetime(today.year, today.month + 1, 1) - timedelta(days=1)
print(last_day_of_month)
本月第一天 = first_day_of_this_month.date()

本月最后一天 = last_day_of_month.date()
print(f"First day of the month: {本月第一天}")
print(f"Last day of the month: {本月最后一天}")


def read():
    import pandas as pd
    pd.set_option('display.max_columns', None)
    # 假设Excel文件名为'example.xlsx'，且有两行表头
    file_name = '../文件/东莞/社保缴交明细2024-04-101712716261.xls'

    # 使用pandas读取Excel文件，header参数设置为[0, 1]

    df = pd.read_excel(file_name, header=[6, 7])

    # 将多级索引转换为一个单级索引
    # 假设你想用第0行和第1行的表头值来创建一个新的索引列
    # 这里假设表头之间没有分隔符，直接拼接
    # df.index = df.index.map(lambda x: '_'.join(map(str, x)))
    # df.columns = ['_'.join(map(str, col)) for col in df.columns.values]
    # df.reset_index(inplace=True)
    columnName = ['序号', '姓名', '证件号码', '证件类型', '个人社保号', '费款所属期起', '费款所属期止',
                  '企业基本养老保险(单位缴纳)_缴费基数', '企业基本养老保险(单位缴纳)_应缴金额',
                  '企业基本养老保险(个人缴纳)_缴费基数',
                  '企业基本养老保险(个人缴纳)_应缴金额', '失业保险(单位缴纳)_缴费基数', '失业保险(单位缴纳)_应缴金额',
                  '失业保险(个人缴纳)_缴费基数', '失业保险(个人缴纳)_应缴金额',
                  '单建统筹职工医保（含生育）（单位缴纳）_缴费基数',
                  '单建统筹职工医保（含生育）（单位缴纳）_应缴金额', '单建统筹职工医保（含生育）（个人缴纳）_缴费基数',
                  '单建统筹职工医保（含生育）（个人缴纳）_应缴金额', '工伤保险_缴费基数', '工伤保险_应缴金额',
                  '特定人员单项工伤保险_缴费基数',
                  '特定人员单项工伤保险_应缴金额', '单位部分合计', '个人部分合计', '应缴金额合计']
    df.columns = columnName
    # 显示结果
    print(df)


# read()
# 拼接社保、社保基数、医保缴交、医保补缴这四个表中的数据
def getmiddle():
    社保待缴人员表路径列表 = ['../文件/烟台/社保缴交明细2024-04-231713832913.xls']
    社保缴交基数表路径 = '../文件/烟台/社保缴交基数2024-04-231713832965.xls'
    医保待缴人员表路径列表 = ['../文件/烟台/医保补缴缴交明细2024-04-231713833063.xlsx',
                              '../文件/烟台/医保人员缴交明细2024-04-231713854485.xlsx']
    社保缴交数据拼接中间表 = '../文件/烟台/社保缴交数据拼接中间表.xlsx'
    str_datemap = {'证件号码': str}  # 设置一些读取excel时读取为str类型的数据
    warnings.filterwarnings('ignore')
    pd.set_option('display.max_columns', None)
    # 读取社保缴交基数的excel
    readJishu = pd.read_excel(社保缴交基数表路径, dtype={'社会保障号码': str, '身份证号码': str})
    for item in 社保待缴人员表路径列表:
        print(f'本次运行的文件路径为：{item}')
        # 读取社保待缴人员表,并且与缴交基数表进行拼接
        readShebaoExcel = pd.read_excel(item, dtype={'社会保障号码': str})
        mergeShebao = pd.merge(readShebaoExcel, readJishu[['社会保障号码', '身份证号码', '缴费基数', '投保类别名称']],
                               how='inner', on=['社会保障号码'])
    print(mergeShebao)
    mergeShebao.rename(columns={"身份证号码": "证件号码"}, inplace=True)
    # readExcel = pd.read_excel(item, header=[6, 7], dtype={'证件号码': str})

    # 读取医疗保险的数据
    # 补缴的和已缴的表头不一样，需要重新设置表头
    yibaoHead = {'个人编号': 'first', '姓名': 'first', '申报类别': 'first', '险种': 'first', '单位缴费基数': 'first',
                 '个人缴费基数': 'first', '单位缴费额': 'sum', '个人缴费额': 'sum'}
    yibaoHeadKeys = yibaoHead.keys()
    for item in 医保待缴人员表路径列表:
        if ('补缴' in item):
            print(f'补缴文件路径:{item}')  # 读取补缴的数据
            readBujiao = pd.read_excel(item, dtype={'个人编号': str})
            readBujiao = readBujiao[yibaoHeadKeys]
            resultGroup = readBujiao.groupby('个人编号').agg(yibaoHead).reset_index(drop=True)
            print(resultGroup)

        else:
            print(f'已缴交文件路径:{item}')  # 读取已缴交人员的数据
            readYijiao = pd.read_excel(item, dtype={'个人编号': str})
            readYijiao = readYijiao[readYijiao['险种'].isin(['职工医疗'])]
            readYijiao = readYijiao[yibaoHeadKeys]
            print(readYijiao)

    concatResult = pd.concat([readYijiao, resultGroup], axis=0)
    resuleYibao =concatResult.groupby(['个人编号']).agg(yibaoHead).reset_index(drop=True)
    print(resuleYibao)
    remeName = {'个人编号': '证件号码', '单位缴费基数': '医保单位缴费基数', '个人缴费基数': '医保个人缴费基数',
                '单位缴费额': '医保单位缴费额', '个人缴费额': '医保个人缴费额'}

    resuleYibao.rename(columns=remeName, inplace=True)
    print(resuleYibao)
    mergeResult = pd.merge(mergeShebao, resuleYibao, how='outer', on=['证件号码', '姓名'])
    mergeResult.fillna(0, inplace=True)
    mergeResult.to_excel(社保缴交数据拼接中间表)


getmiddle()


# 读取社保在缴数据信息写入数据接口模板中
def dataDoing():
    社保数据接口数据 = '../文件/烟台/下载社保数据接口.xls'
    社保缴交数据拼接中间表 = '../文件/烟台/社保缴交数据拼接中间表.xlsx'
    # 社保数据表和数据接口下载表中数据匹配规则Map
    headMap = {'养老保险缴费基数': '缴费基数',
               '养老保险单位交': '企业养老单位缴费额',
               '养老保险个人交': '企业养老个人缴费额',
               '医疗保险缴费基数': '医保单位缴费基数',
               '医疗保险单位交': '医保单位缴费额',
               '医疗保险个人交': '医保个人缴费额',
               '失业保险缴费基数': '缴费基数',
               '失业保险单位交': '失业单位缴费额',
               '失业保险个人交': '失业个人缴费额',
               '工伤保险缴费基数': '缴费基数',
               '工伤保险单位交': '工伤单位缴费',
               }
    str_datemap = {'证件号码': str}  # 设置一些读取excel时读取为str类型的数据
    warnings.filterwarnings('ignore')

    # 读取社保待缴人员表,表头有两行,需要合并表头

    readExcel = pd.read_excel(社保缴交数据拼接中间表, dtype={'社会保障号码': str, '证件号码': str})
    # readExcel.columns = columnName
    # 将读取的数据进行保留两位小数操作
    readExcelList = readExcel['证件号码'].values.tolist()
    print(f'本次读取的证件号码有:{readExcelList}')
    # 读取社保数据接口表中的数据
    readInterfaceExcel = pd.read_excel(社保数据接口数据,
                                       dtype={'人员编码': str, '证件号码': str, '员工号': str})
    heardList = readInterfaceExcel.columns.tolist()
    print(f'当前数据接口模板表的表头为{heardList}')
    # 匹配出社保数据接口中证件号码包含在readExcelList中的数据
    readInterfaceDate = readInterfaceExcel[readInterfaceExcel['证件号码'].isin(readExcelList)]
    print(f'匹配证件号码后的数据为{readInterfaceDate}')

    # 合并两个数据列表,并通过表头字段head ,获取其中需要的几列
    mergeDate = pd.merge(readInterfaceDate, readExcel, how='inner', on=['证件号码'])
    print("*****************")
    # 公积金缴存基数/公积金单位缴存比例 ...等分别重新设置值

    for i in range(0, mergeDate.shape[0]):
        for key, value in headMap.items():
            mergeDate.loc[i, key] = mergeDate.loc[i, value]
        mergeDate.loc[i, '社保单位合计'] = mergeDate.loc[i, '工伤保险单位交'] + \
                                           mergeDate.loc[i, '养老保险单位交'] + \
                                           mergeDate.loc[i, '失业保险单位交'] + \
                                           mergeDate.loc[i, '医疗保险单位交']

        mergeDate.loc[i, '社保个人合计'] = mergeDate.loc[i, '养老保险个人交'] + \
                                           mergeDate.loc[i, '失业保险个人交'] + \
                                           mergeDate.loc[i, '医疗保险个人交']
        mergeDate.loc[i, '社保应收合计'] = mergeDate.loc[i, '社保单位合计'] + mergeDate.loc[i, '社保个人合计']

    resultDate = mergeDate[heardList]

    # # 将'公积金单位缴费'和'公积金个人缴费'字段都保留两位小数
    resultDate['社保单位合计'] = resultDate['社保单位合计'].map(lambda x: round(x, 2))
    resultDate['社保个人合计'] = resultDate['社保个人合计'].map(lambda x: round(x, 2))
    resultDate['社保应收合计'] = resultDate['社保应收合计'].map(lambda x: round(x, 2))
    resultDateList = resultDate.values.tolist()
    print(f'将匹配后并保留小数后的数据转换成列表{resultDateList}')
    # # 获取其中的行数,并将所有行数加1
    indexList = readInterfaceDate.index.tolist()
    # readInterfaceDate.index.item()
    for i in range(0, len(indexList)):
        indexList[i] = indexList[i] + 1
    print(f'处理后的行数列表{indexList}')
    #
    # # 打开xls文件，并在指定行中写入文件
    workbook = xlrd.open_workbook(社保数据接口数据)  # xlrd打开excel
    sheet = workbook.sheet_by_index(0)
    wb = xlwt.Workbook(encoding='utf-8')  # lxwt新建一个sheet页
    new_sheet = wb.add_sheet(sheet.name)
    for row_index in range(sheet.nrows):  # 循环每一行
        row = sheet.row_values(row_index)  # 旧数据中每一行的值
        if row_index in indexList:  # 如果行号与之前存储的匹配数据行列表中的值相同
            index = indexList.index(row_index)
            # 写入新数据到新的sheet
            for col_index, value in enumerate(resultDateList[index]):
                if str(value) == 'nan':
                    value = ''
                new_sheet.write(row_index, col_index, str(value))
            print(f'在{row_index}行中写入新数据:{resultDateList[index]}')
        else:
            # 写入旧数据到新的sheet
            for col_index, value in enumerate(row):
                new_sheet.write(row_index, col_index, value)
    wb.save(社保数据接口数据)


# dataDoing()

def addOther():
    # 将其他的特殊人员写入到表格中
    社保数据接口数据 = '../文件/烟台/下载社保数据接口.xls'
    特殊在保人员文件路径 = '../文件/烟台/特殊社保在保人员.xls'
    warnings.filterwarnings('ignore')
    dtypestr = {'人员编码': str, '证件号码': str, '员工号': str}
    readDate = pd.read_excel(社保数据接口数据, dtype=dtypestr)
    readOtherDate = pd.read_excel(特殊在保人员文件路径, dtype=dtypestr)
    lenth = len(readOtherDate)

    if (lenth > 0):
        concatDate = pd.concat([readDate, readOtherDate], axis=0)
        print('特殊在保人员文件中数据不为空,拼接特殊人员数据')
        print(readOtherDate)
    else:
        concatDate = readDate
        # Log.Info('特殊在保人员文件中数据为空,没有特殊人员往下执行')
        print('特殊在保人员文件中数据为空,没有特殊人员往下执行')
    # 拼接特殊在保人员的数据
    concatDateHead = concatDate.columns.tolist()
    pd.set_option('display.max_columns', None)
    # 设置groupby后的计算规则

    # 将证件号码，这一列的类型装换为str 类型，才可以用groupby进行分组求和
    concatDate['证件号码'] = concatDate['证件号码'].astype(str)
    # # 根据证件号码进行分组求和 as_index
    headFirst = ['员工号', '人员姓名', '证件号码', '人员编码', '部门名称', '人员类别', '入职日期', '岗位', '职层',
                 '开户时间']  # 设置一下数据不用变 的表头用来区分‘first’和‘max’
    for head in concatDateHead:
        if head in headFirst:
            concatDate[head] = concatDate.groupby('证件号码')[head].transform('first')
        else:
            concatDate[head] = concatDate.groupby('证件号码')[head].transform('max')
    print(concatDate)
    # 如果需要，可以删除重复的'A'列值
    concatDate.drop_duplicates(subset='证件号码', inplace=True)
    itemDateframe = concatDate.reset_index(drop=True).fillna('')

    # 所有结果做保留两位有效数字处理
    colList = itemDateframe.columns.tolist()
    nameList = ['社保应收合计', '社保个人合计', '社保单位合计', '养老保险缴费基数', '养老保险个人交', '养老保险单位交',
                '医疗保险缴费基数', '医疗保险个人交', '医疗保险单位交', '工伤保险缴费基数', '工伤保险单位交',
                '失业保险缴费基数', '失业保险个人交', '失业保险单位交', '生育医疗缴费基数', '生育医疗单位交',
                '大病医疗单位', '大病医疗个人', '社保代扣', '公积金缴存基数（元）', '公积金单位缴存比例',
                '公积金个人缴存比例', '公积金单位缴费', '公积金个人缴费', '公积金金额合计（元）', '公积金代扣']

    for name in colList:
        if name in nameList:
            itemDateframe[name] = itemDateframe[name].map(lambda x: round(x, 2))
    Log.Info(self, itemDateframe)
    # itemDateframe.fillna('')
    print(itemDateframe)
    # 写入.xls文件
    wb = xlwt.Workbook(encoding='utf-8')  # lxwt新建一个sheet页
    #    new_sheet = wb.add_sheet(sheet.name)
    sheet = wb.add_sheet('Sheet0')
    # 将DataFrame的列名写入Excel表头
    for i, col_name in enumerate(itemDateframe.columns):
        sheet.write(0, i, col_name)
    # 将DataFrame的数据写入Excel
    for i, row in itemDateframe.iterrows():
        for j, col in enumerate(row):
            sheet.write(i + 1, j, col)
    wb.save(社保数据接口数据)
    # wb.save('../文件/东莞/test.xls')


# addOther()


def checkDate():
    # 核对数据接口数据是否正确
    下载数据接口核对数据 = '../文件/烟台/下载社保数据接口.xls'
    特殊在保人员文件路径 = '../文件/烟台/特殊社保在保人员.xls'
    核对结果文件路径 = '../文件/烟台/核对结果.xlsx'
    # 社保待缴人员表路径列表 = ['../文件/东莞/社保缴交明细2024-04-101712716261.xls']
    社保缴交数据拼接中间表 = '../文件/烟台/社保缴交数据拼接中间表.xlsx'
    pd.set_option('display.max_columns', None)

    warnings.filterwarnings('ignore')

    # 读取社保数据接口表中的数据
    str_datemap = {'人员编码': str, '证件号码': str, '人员姓名': str, '员工号': str}  # 设置一些读取excel时读取为str类型的数据

    readInterfaceExcel = pd.read_excel(下载数据接口核对数据, usecols='A:AE', dtype=str_datemap)
    print(f'本次下载的数据接口核对数据有{len(readInterfaceExcel)}条')
    print(readInterfaceExcel)
    readOtherExcel = pd.read_excel(特殊在保人员文件路径, usecols='A:AE', dtype=str_datemap)
    print(f'本次属于特殊人员的数据有{len(readOtherExcel)}条')

    lenth = len(readOtherExcel)
    if (lenth > 0):
        readInterfaceExcel = pd.concat([readInterfaceExcel, readOtherExcel], axis=0)
        print('特殊在保人员文件中数据不为空,拼接特殊人员数据')
    # 拼接特殊人员表后,如果两行全部数据都想相同的,就去除一行
    readDate = readInterfaceExcel.drop_duplicates(keep='first')
    print(readDate)

    resultDateList = []

    # 读取社保待缴人员表
    readExcel = pd.read_excel(社保缴交数据拼接中间表, dtype={'社会保障号码': str, '证件号码': str})
    readExcel.fillna(0, inplace=True)

    caoncatDate = readExcel
    print(f'本次属于在缴人员的数据有{len(caoncatDate)}条')
    print(caoncatDate)
    mergeDate = pd.merge(readDate, caoncatDate, how='outer', on=['证件号码'])
    print(mergeDate)
    # print(mergeDate.columns)

    # 可以根据需要添加更多列对(需要相互比较的列明)
    headMap = {'养老保险缴费基数': '缴费基数',
               '养老保险单位交': '企业养老单位缴费额',
               '养老保险个人交': '企业养老个人缴费额',
               '医疗保险缴费基数': '医保单位缴费基数',
               '医疗保险单位交': '医保单位缴费额',
               '医疗保险个人交': '医保个人缴费额',
               '失业保险缴费基数': '缴费基数',
               '失业保险单位交': '失业单位缴费额',
               '失业保险个人交': '失业个人缴费额',
               '工伤保险缴费基数': '缴费基数',
               '工伤保险单位交': '工伤单位缴费',
               }

    # 使用列表解析来创建条件 判断是否相等,不相等的为ture ,相等的为false
    # 将headMap转换为list类型的元组:[('a', 1), ('b', 2), ('c', 3)]
    column_pairs = [(key, value) for key, value in headMap.items()]
    # 遍历元组
    conditions = [mergeDate[col1] != mergeDate[col2] for col1, col2 in column_pairs]
    # print(conditions)
    print(conditions)
    # # 使用any函数来检查任何条件是否为True
    mask = pd.concat(conditions, axis=1).any(axis=1)
    #
    # # 使用布尔mask来筛选行
    filtered_df = mergeDate[mask]
    print(f'得到下载数据与缴交数据不相同的数据有{len(filtered_df)}条')
    filtered_df.to_excel(核对结果文件路径)

# checkDate()


